import openpyxl
import requests
import time
import os

# 配置
INPUT_FILE = r'c:\Users\Administrator\Desktop\ip_pool\ip_detail.xlsx'
OUTPUT_FILE = r'c:\Users\Administrator\Desktop\ip_pool\available_ips.xlsx'
TIMEOUT = 5
MAX_RETRIES = 2

print(f"开始IP可用性检查")
print(f"输入文件: {INPUT_FILE}")

# 检查输入文件是否存在
if not os.path.exists(INPUT_FILE):
    print(f"错误: 输入文件不存在")
    exit(1)

# 读取Excel文件
print("正在读取Excel文件...")
try:
    workbook = openpyxl.load_workbook(INPUT_FILE)
    sheet = workbook.active
    print(f"成功读取Excel文件，工作表名称: {sheet.title}")
    
    # 获取总行数
    total_rows = sheet.max_row
    print(f"总行数: {total_rows}")
    
    # 读取第一列的IP地址
    ip_list = []
    for row in range(2, total_rows + 1):  # 从第2行开始，假设第1行是标题
        cell_value = sheet.cell(row=row, column=1).value
        if cell_value is not None:
            ip = str(cell_value).strip()
            ip_list.append(ip)
    
    print(f"成功读取 {len(ip_list)} 个IP地址")
    print(f"前5个IP: {ip_list[:5]}")
    
    # 检查IP可用性
    available_ips = []
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
    }
    
    for i, ip in enumerate(ip_list):
        print(f"\n检查 IP {i+1}/{len(ip_list)}: {ip}")
        is_available = False
        
        # 验证IP格式
        if '.' not in ip:
            print(f"跳过: 无效的IP格式")
            continue
        
        proxies = {
            'http': f'http://{ip}',
            'https': f'https://{ip}'
        }
        
        for retry in range(MAX_RETRIES):
            try:
                print(f"  尝试 {retry+1}/{MAX_RETRIES}...")
                response = requests.get(
                    'https://www.baidu.com',
                    proxies=proxies,
                    headers=headers,
                    timeout=TIMEOUT,
                    allow_redirects=True
                )
                
                if response.status_code == 200:
                    print(f"  ✓ 成功: 状态码 200")
                    is_available = True
                    break
                else:
                    print(f"  ✗ 失败: 状态码 {response.status_code}")
                    break
                    
            except requests.exceptions.RequestException as e:
                print(f"  ✗ 异常: {str(e)}")
                if retry < MAX_RETRIES - 1:
                    print(f"  等待1秒后重试...")
                    time.sleep(1)
        
        if is_available:
            available_ips.append(ip)
            print(f"  IP {ip} 可用，已添加到结果列表")
    
    # 保存可用IP到新的Excel文件
    print(f"\n检查完成!")
    print(f"可用IP数量: {len(available_ips)}")
    
    if available_ips:
        print(f"正在保存到输出文件: {OUTPUT_FILE}")
        new_workbook = openpyxl.Workbook()
        new_sheet = new_workbook.active
        new_sheet.title = "可用IP"
        
        # 添加标题
        new_sheet.cell(row=1, column=1).value = "Available_IP"
        
        # 添加可用IP
        for i, ip in enumerate(available_ips):
            new_sheet.cell(row=i+2, column=1).value = ip
        
        new_workbook.save(OUTPUT_FILE)
        print(f"✓ 可用IP已成功保存到 {OUTPUT_FILE}")
    else:
        print("没有找到可用的IP地址")
        
except Exception as e:
    print(f"发生错误: {str(e)}")
    import traceback
    traceback.print_exc()

print("\n脚本执行结束")